package sealte.hvpms.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import sealte.hvpms.dao.impl.PollOptionDAOImpl;
import sealte.hvpms.dao.impl.RulesDAOImpl;
import sealte.hvpms.dao.PollDAO;
import sealte.hvpms.pojo.Poll;
import sealte.hvpms.util.DBConnection;

public class PollDAOImpl implements PollDAO {
	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	
	public boolean addNew(Poll poll){
		boolean flag = false;
		String sql = "insert into hvpms_polls(poll_name,poll_maxanswers,poll_descriptions,poll_builder,poll_enddate,poll_rules) values(?,?,?,?,?,?)";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, poll.getName());
			ps.setInt(2, poll.getMaxanswers());
			ps.setString(3, poll.getDescriptions());
			ps.setInt(4, poll.getBuilder());
			ps.setDate(5, poll.getEnddate());
			ps.setInt(6, poll.getRule_int());
			ps.executeUpdate();
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null,ps, conn);
		}
		return flag;
	}
	
	public boolean isDuplicated(String name,int builder){
		boolean flag = false;
		String sql = "select * from hvpms_polls where poll_name=? and poll_builder=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setInt(2, builder);
			rs = ps.executeQuery();
			if(rs.next()){
				flag = true;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return flag;
	}
	
	public int getID(String name,int builder){
		int id = 0;
		String sql = "select poll_id from hvpms_polls where poll_name=? and poll_builder=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setInt(2, builder);
			rs = ps.executeQuery();
			if(rs.next()){
				id = rs.getInt("poll_id");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return id;
	}
	
	public int getAllRecorders(String kw,int builder){
		int ar = 0;
		String sql = null;
		if(kw==null||kw.equals("")){
			sql = "SELECT COUNT(poll_id) from hvpms_polls where poll_builder=?";
		}else{
			sql = "SELECT COUNT(poll_id) from hvpms_polls WHERE poll_builder=? AND (poll_name LIKE ? OR poll_isenabled LIKE ? OR poll_isexpired LIKE ?)";
		}
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			if (!(kw == null || kw.equals(""))){
				ps.setInt(1, builder);
				ps.setString(2, "%" + kw + "%");
				ps.setString(3, "%" + kw + "%");
				ps.setString(4, "%" + kw + "%");
			}else{
				ps.setInt(1, builder);
			}
			rs = ps.executeQuery();
			if(rs.next()){
				ar = rs.getInt(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return ar;
	}
	
	private void setIsexpired(int poll_id,int builder){
		String sql = "update hvpms_polls set poll_isexpired=? where poll_id=? and poll_builder=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, "Y");
			ps.setInt(2, poll_id);
			ps.setInt(3, builder);
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null,ps, conn);
		}
	}
	
	public List<Poll> listAll(int currentPage,int lineSize,String kw,int builder){
		List<Poll> all = new ArrayList<Poll>();	
		int prevDates = (currentPage -1) * lineSize;
		String sql = null;
		Date nowDate = new Date();
		if (kw == null || kw.equals("")) {
			sql = "SELECT * FROM hvpms_polls WHERE poll_builder=? limit ?,?";
		} else {
			sql = "SELECT * FROM hvpms_polls WHERE poll_builder=? AND (poll_name LIKE ? OR poll_isenabled LIKE ? OR poll_isexpired LIKE ?) limit ?,?";
		}
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			if (!(kw == null || kw.equals(""))) {
				ps.setInt(1, builder);
				ps.setString(2, "%" + kw + "%");
				ps.setString(3, "%" + kw + "%");
				ps.setString(4, "%" + kw + "%");
				ps.setInt(5, prevDates);
				ps.setInt(6, lineSize);
			}else{
				ps.setInt(1, builder);
				ps.setInt(2, prevDates);
				ps.setInt(3, lineSize);
			}
			rs = ps.executeQuery();
			while(rs.next()){
				Poll poll = new Poll();
				Date date = rs.getDate("poll_enddate");
				int poll_id = rs.getInt("poll_id");
				if(nowDate.after(date)){
					poll.setIsexpired("Y");
					this.setIsexpired(poll_id, builder);
				}else{
					poll.setIsexpired("N");
				}
				poll.setId(poll_id);
				poll.setName(rs.getString("poll_name"));
				poll.setIsenabled(rs.getString("poll_isenabled"));
				poll.setBuilddate(rs.getDate("poll_builddate"));
				poll.setEnddate(rs.getDate("poll_enddate"));
				poll.setRule_int(rs.getInt("poll_rules"));
				poll.setRule_str((new RulesDAOImpl()).getContentByID(rs.getInt("poll_rules")));
				poll.setAll_polls((new PollOptionDAOImpl()).getAll_polls(poll_id));
				all.add(poll);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return all;
	}
	
	public boolean batchDeleteByID(int[] poll_ids,int builder){
		boolean flag = false;
		String sql = "delete from hvpms_polls where poll_id=? AND poll_builder=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			for(int i=0;i<poll_ids.length;i++){
				ps.setInt(1, poll_ids[i]);
				ps.setInt(2, builder);
				ps.executeUpdate();
			}
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null,ps, conn);
		}
		return flag;
	}
	
	public boolean batchModifyIsenabledByID(int[] poll_ids,int builder,String enabled){
		boolean flag = false;
		String sql = "update hvpms_polls set poll_isenabled=? where poll_id=? AND poll_builder=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			for(int i=0;i<poll_ids.length;i++){
				ps.setString(1, enabled);
				ps.setInt(2, poll_ids[i]);
				ps.setInt(3, builder);
				ps.executeUpdate();
			}
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(null,ps, conn);
		}
		return flag;
	}
	
	public Poll getPoll(int poll_id){
		Poll poll = null;
		String sql = "select * from hvpms_polls where poll_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			rs = ps.executeQuery();
			if(rs.next()){
				poll = new Poll();
				poll.setName(rs.getString("poll_name"));
				poll.setDescriptions(rs.getString("poll_descriptions"));
				poll.setIsenabled(rs.getString("poll_isenabled"));
				poll.setIsexpired(rs.getString("poll_isexpired"));
				poll.setMaxanswers(rs.getInt("poll_maxanswers"));
				poll.setRule_int(rs.getInt("poll_rules"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return poll;
	}
	
	public int getRuleById(int poll_id){
		int rule = 0;
		String sql = "select poll_rules from hvpms_polls where poll_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			rs = ps.executeQuery();
			if(rs.next()){
				rule = rs.getInt("poll_rules");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return rule;
	}
	
	public int getPollRuleByID(int poll_id){
		int rule = 0;
		String sql = "select poll_rules from hvpms_polls where poll_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			rs = ps.executeQuery();
			if(rs.next()){
				rule = rs.getInt("poll_rules");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return rule;
	}
	
	public String getNameByID(int poll_id){
		String name = "";
		String sql = "select poll_name from hvpms_polls where poll_id=?";
		try{
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, poll_id);
			rs = ps.executeQuery();
			if(rs.next()){
				name = rs.getString("poll_name");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.close(rs, ps, conn);
		}
		return name;
	}
}
